IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[csp_rptDepartmentalMetric_PCROAwardsToBeActivatedDetails]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[csp_rptDepartmentalMetric_PCROAwardsToBeActivatedDetails]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  
/*
**************************************************************************
* Name:          csp_rptDepartmentalMetric_PCROAwardsToBeActivatedDetails
* Purpose:        Get data for report
* Change History:
* Date            By          Change
* 08/26/2009       aab       Added New stored proc
**************************************************************************
*/ 

CREATE  PROCEDURE [dbo].[csp_rptDepartmentalMetric_PCROAwardsToBeActivatedDetails]   

(      
  @PeriodType VARCHAR(50)      
, @PeriodStartDate SMALLDATETIME      
 ,@Institution varchar (50) = NULL
, @Department INT = NULL    
, @Unit VARCHAR(250)  = NULL
, @PrincipalInvestigator VARCHAR(50)=NULL    
, @Sponsor  VARCHAR(150) = NULL    
, @FundNumber VARCHAR(50) = NULL
, @DomainUserId INT =  NULL
)      
AS    
 
BEGIN 
SELECT
B.PeriodType,
B.PeriodStartDate,
ai.Institution,
B.ProposalNumber,
ai.PrincipalInvestigator  AS PIName,
B.ChiefCode,
B.Sponsor,
B.SponsorID,
B.ProposalStatus,
B.ExecutedDate,
B.UploadType,
B.UploadDate,
B.UploadedBy,
B.CoversheetUploadDate,
B.CoversheetUploadedBy,
B.PostAwardManager,
B.LastActivityComment,
B.LastActivityDate,
B.LastActivityEnterer

FROM  rptMetric_PCROAwardsToBeActivated  B
INNER JOIN INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements (@DomainUserId) sec 
			ON b.ProposalNumber = sec.FolderNumber
INNER JOIN INSIGHT_RPT_DB.dbo.AgrAgreement_Info ai
			ON sec.FolderNumber = ai.FolderNumber
WHERE
			(ai.institution= @Institution  OR @Institution IS NULL OR @Institution = 'All')
			AND (ai.DepartmentId = @Department OR @Department IS NULL OR @Department=0 )
			AND (ai.UnitId in (SELECT * FROM dbo.fnSplitFundNumbers(@Unit)) OR @Unit IS NULL OR @Unit=0 OR @Unit='') 
			AND  b.PeriodStartDate = @PeriodStartDate   
			AND (ai.PrincipalInvestigatorId = @PrincipalInvestigator OR @PrincipalInvestigator IS NULL OR @PrincipalInvestigator=''OR @PrincipalInvestigator='0')    
			AND  (ai.Sponsorid = @Sponsor OR @Sponsor IS NULL OR @Sponsor='' OR @Sponsor='0')
			AND (ai.ProjectFundNumber IN (SELECT * FROM dbo.fnSplitFundNumbers(@FundNumber)) OR @FundNumber IS NULL OR @FundNumber='')
			AND b.PeriodType=@PeriodType  
 


END 

GO

